-- =========================
-- SEQUENCES
-- =========================
CREATE SEQUENCE RAG_CONTINENT_ID_SEQ START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE SEQUENCE RAG_COUNTRY_ID_SEQ START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE SEQUENCE RAG_CITY_ID_SEQ START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE SEQUENCE RAG_PERSON_ID_SEQ START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

-- =========================
-- TABLES
-- =========================
CREATE TABLE RAG_CONTINENT (
  ID NUMBER,
  CODE VARCHAR2(20) NOT NULL,
  NAME VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK VARCHAR2(200) DEFAULT '',
  AREA NUMBER DEFAULT 0,
  POPULATION NUMBER DEFAULT 0,
  COUNTRIES_NUMBER NUMBER DEFAULT 0,
  CONSTRAINT RAG_CONTINENT_PK PRIMARY KEY (ID)
);

CREATE TABLE RAG_COUNTRY (
  ID NUMBER,
  NAME VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK VARCHAR2(200) DEFAULT '',
  CONTINENT_ID NUMBER,
  ISO_NUMERIC VARCHAR2(50) NOT NULL,
  ISO_ALPHA2 VARCHAR2(50) NOT NULL,
  ISO_ALPHA3 VARCHAR2(50) NOT NULL,
  FLAG VARCHAR2(50),
  CONSTRAINT RAG_COUNTRY_PK PRIMARY KEY (ID),
  CONSTRAINT RAG_COUNTRY_FK_CONTINENT FOREIGN KEY (CONTINENT_ID) REFERENCES RAG_CONTINENT(ID)
);

CREATE TABLE RAG_CITY (
  ID NUMBER,
  NAME VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK VARCHAR2(200) NOT NULL,
  COUNTRY_ID NUMBER,
  CAPITAL NUMBER(1) DEFAULT 0,
  CONSTRAINT RAG_CITY_PK PRIMARY KEY (ID),
  CONSTRAINT RAG_CITY_FK_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES RAG_COUNTRY(ID)
);

CREATE TABLE RAG_PERSON (
  ID NUMBER,
  NAME VARCHAR2(50) NOT NULL,
  WIKIPEDIA_LINK VARCHAR2(200) NOT NULL,
  BIRTH_DATE DATE,
  BIRTH_CITY_ID NUMBER,
  DEATH_DATE DATE,
  DEATH_CITY_ID NUMBER,
  GENDER_ID NUMBER,
  DESCRIPTION CLOB,
  IMAGE VARCHAR2(200),
  CONSTRAINT RAG_PERSON_PK PRIMARY KEY (ID),
  CONSTRAINT RAG_PERSON_FK_BIRTH_CITY FOREIGN KEY (BIRTH_CITY_ID) REFERENCES RAG_CITY(ID),
  CONSTRAINT RAG_PERSON_FK_DEATH_CITY FOREIGN KEY (DEATH_CITY_ID) REFERENCES RAG_CITY(ID)
);

-- =========================
-- TRIGGERS
-- =========================
CREATE
OR REPLACE TRIGGER RAG_CONTINENT_BI BEFORE
INSERT
  ON RAG_CONTINENT FOR EACH ROW BEGIN IF :NEW.ID IS NULL THEN :NEW.ID := RAG_CONTINENT_ID_SEQ.NEXTVAL;

END IF;

END;

/ CREATE
OR REPLACE TRIGGER RAG_COUNTRY_BI BEFORE
INSERT
  ON RAG_COUNTRY FOR EACH ROW BEGIN IF :NEW.ID IS NULL THEN :NEW.ID := RAG_COUNTRY_ID_SEQ.NEXTVAL;

END IF;

END;

/ CREATE
OR REPLACE TRIGGER RAG_CITY_BI BEFORE
INSERT
  ON RAG_CITY FOR EACH ROW BEGIN IF :NEW.ID IS NULL THEN :NEW.ID := RAG_CITY_ID_SEQ.NEXTVAL;

END IF;

END;

/ CREATE
OR REPLACE TRIGGER RAG_PERSON_BI BEFORE
INSERT
  ON RAG_PERSON FOR EACH ROW BEGIN IF :NEW.ID IS NULL THEN :NEW.ID := RAG_PERSON_ID_SEQ.NEXTVAL;

END IF;

END;

/